﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Common;
using DBUtility;
using Models;

namespace BaseDal
{
    /// <summary>
    /// PurchasePayApplyDal 类实现了付款申请的数据访问层。
    /// 该类采用单例模式，确保只有一个实例存在。
    /// </summary>
    public class PurchasePayApplyDal
    {
        // 使用 Lazy<T> 实现单例模式，确保实例的惰性初始化和线程安全。
        private static readonly Lazy<PurchasePayApplyDal> _instance = new Lazy<PurchasePayApplyDal>(() => new PurchasePayApplyDal());

        /// <summary>
        /// 获取类的单例实例。
        /// </summary>
        public static PurchasePayApplyDal Instance { get; } = _instance.Value;

        // 私有构造函数，防止外部实例化该类。
        private PurchasePayApplyDal()
        {
        }

        private const string tableH = "PurchasePayApplyHead";

        private const string tableB = "PurchasePayApplyBody";

        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public bool IsExist(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + tableH);
            strSql.Append(" where IsDelete=0 " + strWhere);
            return SqlHelper.Exists(strSql.ToString());
        }

        /// <summary>
        /// 查询SQL语句
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public StringBuilder GetSelectSql(string strWhere = "")
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID,PPA_Number,PPA_Date,PPA_Person,PPA_CompanyId,PPA_CompanyName,PPA_DeptId,PPA_DeptName,");
            sb.Append("PPA_SupplierId,PPA_SupplierName,PPA_Bank,PPA_BankNumber,PPA_Remark,PPA_AuditStatus,PPA_PayAmount ");
            sb.Append("FROM " + tableH);
            sb.Append(" Where IsDelete=0 ");

            if (!string.IsNullOrEmpty(strWhere))
            {
                sb.Append(strWhere);
            }

            return sb;
        }

        /// <summary>
        /// 查询分页数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable GetPageList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            string countSql = PageHelper.CreateCountingSql(sb.ToString());
            recordCount = Convert.ToInt32(SqlHelper.GetSingle(countSql));
            string pageSql = PageHelper.CreatePageSql(recordCount, pageSize, pageIndex, sb.ToString(), filedOrder);
            DataSet ds = SqlHelper.Query(pageSql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 查询数据集合
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public List<PurchasePayApplyHead> GetList(string strWhere)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            DataSet ds = SqlHelper.Query(sb.ToString());
            return CommonOperation.ConvertDataTableToModelList<PurchasePayApplyHead>(ds.Tables[0]);
        }

        /// <summary>
        /// 根据id查询实体类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public PurchasePayApplyHead GetModelById(decimal id)
        {
            // 查询主表
            PurchasePayApplyHead model;
            StringBuilder sb = GetSelectSql(" and ID=@id");

            SqlParameter[] parameters =
            {
                new SqlParameter("@id",id)
            };
            DataSet ds = SqlHelper.Query(sb.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model = CommonOperation.DataTableToModel<PurchasePayApplyHead>(ds.Tables[0]);
                // 查询子表
                model.ItemList = GetItemList((int)model.ID);

                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据主表ID查询所有物品
        /// </summary>
        /// <param name="headID"></param>
        /// <returns></returns>
        public List<PurchasePayApplyBody> GetItemList(int headID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID,HeadID,PPA_ApplyNo,PPA_ItemID,PPA_ItemName,PPA_Specs,PPA_Unit,PPA_Price,PPA_ApplyQty,");
            sb.Append("PPA_GetQty,PPA_NotGetQty,PPA_Paid,PPA_Pay,PPA_TotalCost ");
            sb.Append("FROM " + tableB);
            sb.Append(" Where IsDelete=0 and HeadID=@headID ");
            SqlParameter[] param1 = { new SqlParameter("@headID", headID) };
            DataSet ds = SqlHelper.Query(sb.ToString(), param1);
            return CommonOperation.ConvertDataTableToModelList<PurchasePayApplyBody>(ds.Tables[0]);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool AddPurchasePayApply(PurchasePayApplyHead model)
        {
            // 定义主表SQL语句和参数
            StringBuilder hSql = new StringBuilder();
            hSql.Append("insert into " + tableH + " (");
            hSql.Append("PPA_Number,PPA_Date,PPA_Person,PPA_CompanyId,PPA_CompanyName,PPA_DeptId,PPA_DeptName,");
            hSql.Append("PPA_SupplierId,PPA_SupplierName,PPA_Bank,PPA_BankNumber,PPA_Remark,PPA_AuditStatus,PPA_PayAmount)");
            hSql.Append(" OUTPUT inserted.ID values (");
            hSql.Append("@number,@date,@person,@companyId,@companyName,@deptId,@deptName,");
            hSql.Append("@supplierId,@supplierName,@bank,@bankNumber,@remark,@auditStatus,@payAmount)");

            string connectionString = SqlHelper.connectionString;
            Action<SqlConnection, SqlTransaction> sqlAction = (connection, transaction) =>
            {
                using (SqlCommand command1 = new SqlCommand(hSql.ToString(), connection, transaction))
                {
                    command1.CommandType = CommandType.Text;
                    command1.Parameters.AddWithValue("@number", model.PPA_Number);
                    command1.Parameters.AddWithValue("@date", model.PPA_Date);
                    command1.Parameters.AddWithValue("@person", model.PPA_Person);
                    command1.Parameters.AddWithValue("@companyId", model.PPA_CompanyId);
                    command1.Parameters.AddWithValue("@companyName", model.PPA_CompanyName);
                    command1.Parameters.AddWithValue("@deptId", model.PPA_DeptId);
                    command1.Parameters.AddWithValue("@deptName", model.PPA_DeptName);
                    command1.Parameters.AddWithValue("@supplierId", model.PPA_SupplierId);
                    command1.Parameters.AddWithValue("@supplierName", model.PPA_SupplierName);
                    command1.Parameters.AddWithValue("@bank", model.PPA_Bank);
                    command1.Parameters.AddWithValue("@bankNumber", model.PPA_BankNumber);
                    command1.Parameters.AddWithValue("@remark", model.PPA_Remark);
                    command1.Parameters.AddWithValue("@auditStatus", model.PPA_AuditStatus);
                    command1.Parameters.AddWithValue("@payAmount", model.PPA_PayAmount);
                    command1.Connection = connection; // 确保连接已设置

                    // 执行命令并获取自增长ID
                    object result = command1.ExecuteScalar();
                    long newID = 0;
                    if (result != null && result is decimal) // 检查是否为null并且能否转换为decimal
                    {
                        newID = Convert.ToInt64(result);
                        model.ID = newID;
                    }

                    // 子表数据
                    List<PurchasePayApplyBody> itemList = model.ItemList;
                    if (itemList != null && itemList.Count > 0)
                    {
                        SqlCommand command2;

                        StringBuilder itemSql;
                        foreach (PurchasePayApplyBody itemModel in itemList)
                        {
                            itemSql = new StringBuilder();
                            itemSql.Append("insert into " + tableB + "(");
                            itemSql.Append("HeadID,PPA_ItemID,PPA_ItemName,PPA_Specs,PPA_Unit,PPA_Price,PPA_ApplyQty,PPA_GetQty,PPA_NotGetQty,PPA_Paid,PPA_Pay,");
                            itemSql.Append("PPA_TotalCost,PPA_ApplyNo)");
                            itemSql.Append(" values (");
                            itemSql.Append("@headID,@itemID,@itemName,@specs,@unit,@price,@applyQty,@getQty,@notGetQty,@paid,@pay,@totalCost,@applyno)");

                            using (command2 = new SqlCommand(itemSql.ToString(), connection, transaction))
                            {
                                command2.CommandType = CommandType.Text;
                                command2.Parameters.AddWithValue("@headID", newID);
                                command2.Parameters.AddWithValue("@itemID", itemModel.PPA_ItemID);
                                command2.Parameters.AddWithValue("@itemName", itemModel.PPA_ItemName);
                                command2.Parameters.AddWithValue("@specs", itemModel.PPA_Specs);
                                command2.Parameters.AddWithValue("@unit", itemModel.PPA_Unit);
                                command2.Parameters.AddWithValue("@price", itemModel.PPA_Price);
                                command2.Parameters.AddWithValue("@applyQty", itemModel.PPA_ApplyQty);
                                command2.Parameters.AddWithValue("@getQty", itemModel.PPA_GetQty);
                                command2.Parameters.AddWithValue("@notGetQty", itemModel.PPA_NotGetQty);
                                command2.Parameters.AddWithValue("@paid", itemModel.PPA_Paid);
                                command2.Parameters.AddWithValue("@pay", itemModel.PPA_Pay);
                                command2.Parameters.AddWithValue("@totalCost", itemModel.PPA_TotalCost);
                                command2.Parameters.AddWithValue("@applyno", itemModel.PPA_ApplyNo);

                                command2.ExecuteNonQuery();
                            }
                        }
                    }
                }
            };

            // 调用DatabaseHelper中的静态方法ExecuteTransaction
            SqlHelper.ExecuteTransaction(sqlAction, connectionString);

            return true;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdatePurchasePayApply(PurchasePayApplyHead model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + tableH + " set ");
            strSql.Append("PPA_Date=@date,");
            strSql.Append("PPA_Person=@person,");
            strSql.Append("PPA_CompanyId=@companyId,");
            strSql.Append("PPA_CompanyName=@companyName,");
            strSql.Append("PPA_DeptId=@deptId,");
            strSql.Append("PPA_DeptName=@deptName,");
            strSql.Append("PPA_SupplierId=@supplierId,");
            strSql.Append("PPA_SupplierName=@supplierName,");
            strSql.Append("PPA_Bank=@bank,");
            strSql.Append("PPA_BankNumber=@bankNumber,");
            strSql.Append("PPA_Remark=@remark,");
            strSql.Append("PPA_AuditStatus=@auditStatus,");
            strSql.Append("PPA_PayAmount=@payAmount ");
            strSql.Append(" where ID=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@date", model.PPA_Date),
                    new SqlParameter("@person", model.PPA_Person),
                    new SqlParameter("@companyId", model.PPA_CompanyId),
                    new SqlParameter("@companyName", model.PPA_CompanyName),
                    new SqlParameter("@deptId", model.PPA_DeptId),
                    new SqlParameter("@deptName", model.PPA_DeptName),
                    new SqlParameter("@supplierId", model.PPA_SupplierId),
                    new SqlParameter("@supplierName", model.PPA_SupplierName),
                    new SqlParameter("@bank", model.PPA_Bank),
                    new SqlParameter("@bankNumber", model.PPA_BankNumber),
                    new SqlParameter("@remark", model.PPA_Remark),
                    new SqlParameter("@auditStatus", model.PPA_AuditStatus),
                    new SqlParameter("@payAmount", model.PPA_PayAmount),
                    new SqlParameter("@id", model.ID)
            };
            List<CommandInfo> sqllist = new List<CommandInfo>();
            CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
            sqllist.Add(cmd);

            //先删除子表数据
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("delete from " + tableB + " where HeadID=@headID ");
            SqlParameter[] parameters2 = { new SqlParameter("@headID", model.ID) };
            cmd = new CommandInfo(strSql2.ToString(), parameters2);
            sqllist.Add(cmd);

            //添加子表数据
            if (model.ItemList != null)
            {
                StringBuilder itemSql;
                foreach (PurchasePayApplyBody modelt in model.ItemList)
                {
                    itemSql = new StringBuilder();
                    itemSql.Append("insert into " + tableB + "(");
                    itemSql.Append("HeadID,PPA_ItemID,PPA_ItemName,PPA_Specs,PPA_Unit,PPA_Price,PPA_ApplyQty,PPA_GetQty,PPA_NotGetQty,PPA_Paid,PPA_Pay,");
                    itemSql.Append("PPA_TotalCost,PPA_ApplyNo)");
                    itemSql.Append(" values (");
                    itemSql.Append("@headID,@itemID,@itemName,@specs,@unit,@price,@applyQty,@getQty,@notGetQty,@paid,@pay,@totalCost,@applyno)");
                    SqlParameter[] parameters3 = {
                            new SqlParameter("@headID", model.ID),
                            new SqlParameter("@itemID", modelt.PPA_ItemID),
                            new SqlParameter("@itemName", modelt.PPA_ItemName),
                            new SqlParameter("@specs", modelt.PPA_Specs),
                            new SqlParameter("@unit", modelt.PPA_Unit),
                            new SqlParameter("@price", modelt.PPA_Price),
                            new SqlParameter("@applyQty", modelt.PPA_ApplyQty),
                            new SqlParameter("@getQty", modelt.PPA_GetQty),
                            new SqlParameter("@notGetQty", modelt.PPA_NotGetQty),
                            new SqlParameter("@paid", modelt.PPA_Paid),
                            new SqlParameter("@pay", modelt.PPA_Pay),
                            new SqlParameter("@totalCost", modelt.PPA_TotalCost),
                            new SqlParameter("@applyno", modelt.PPA_ApplyNo)
                };
                    cmd = new CommandInfo(itemSql.ToString(), parameters3);
                    sqllist.Add(cmd);
                }
            }
            int rowsAffected = SqlHelper.ExecuteSqlTran(sqllist);
            return rowsAffected > 0;
        }

        /// <summary>
        /// 删除一条数据，及子表所有相关数据
        /// </summary>
        public bool DeletePurchasePayApply(decimal ID)
        {
            List<CommandInfo> sqllist = new List<CommandInfo>();
            //删除子表
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + tableB + " set IsDelete=1");
            strSql.Append(" where HeadID=@headID");
            SqlParameter[] parameters = { new SqlParameter("@headID", ID) };
            CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
            sqllist.Add(cmd);
            //删除主表
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("update " + tableH + " set IsDelete=1");
            strSql2.Append(" where ID=@id");
            SqlParameter[] parameters2 = { new SqlParameter("@id", ID) };
            cmd = new CommandInfo(strSql2.ToString(), parameters2);
            sqllist.Add(cmd);

            int rowsAffected = SqlHelper.ExecuteSqlTran(sqllist);
            return rowsAffected > 0;
        }

        /// <summary>
        /// 修改审核状态（0：审批中；1：已审批；2：暂存）
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateStatus(int id, int status)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Update " + tableH + " SET ");
            sb.Append("PPA_AuditStatus=@status ");
            sb.Append("WHERE ID=@id");
            SqlParameter[] parameters = {
                new SqlParameter("@status",status),
                new SqlParameter("@id",id),
            };
            int result = SqlHelper.ExecuteSql(sb.ToString(), parameters);
            return result;
        }

    }
}
